using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Dynamic;
using System.Data.SqlClient;
using System.Configuration;
using Model;
using Common;

namespace DAL
{
    public class MyQuanDAL
    {
        public static int Save(MyQuanInfo info)
        {
            string sql = string.Format("insert into [myquan]([AdminID],[UserID],[QuanID],[Money],[Man],[State],[BeginDate],[EndDate],[CreateDate]) values(@AdminID,@UserID,@QuanID,@Money,@Man,@State,@BeginDate,@EndDate,@CreateDate);select @@identity;");
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@AdminID", info.AdminID),
                new SqlParameter("@UserID", info.UserID),
                new SqlParameter("@QuanID", info.QuanID),
                new SqlParameter("@Money", info.Money),
                new SqlParameter("@Man", info.Man),
                new SqlParameter("@State", info.State),
                new SqlParameter("@BeginDate", info.BeginDate),
                new SqlParameter("@EndDate", info.EndDate),
                new SqlParameter("@CreateDate", info.CreateDate)
            };
            return DataConvert.ToInt(CommonDAL.ExecuteScalar(sql, paras));
        }
        public static int Update(MyQuanInfo info)
        {
            string sql = string.Format("update [myquan] set [AdminID]=@AdminID,[Money]=@Money,[Man]=@Man,[BeginDate]=@BeginDate,[EndDate]=@EndDate where [ID]=@ID");
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@AdminID", info.AdminID),
                new SqlParameter("@Money", info.Money),
                new SqlParameter("@Man", info.Man),
                new SqlParameter("@BeginDate", info.BeginDate),
                new SqlParameter("@EndDate", info.EndDate),
                new SqlParameter("@ID", info.ID)
            };
            return CommonDAL.ExecuteNoneQuery(sql, paras) ? info.ID : 0;
        }
        public static MyQuanInfo FindOneByID(int id)
        {
            DataRow dr = CommonDAL.FindOneByID(id, "[myquan]");
            return SimpleData(dr);
        }
        public static List<MyQuanInfo> FindListByIDs(string ids)
        {
            DataTable dt = CommonDAL.FindListByIDs(ids, "[myquan]");
            return ListSimpleData(dt);
        }
        public static bool IsExist(int adminid, int userid, int quanid)
        {
            string sql = string.Format("select ID from [myquan] where AdminID={0} and UserID={1} and QuanID={2}", adminid, userid, quanid);
            return DataConvert.ToInt(CommonDAL.ExecuteScalar(sql)) > 0 ? true : false;
        }
        public static MyQuanInfo FindMaxQuan(int adminid, int userid, double man)
        {
            string sql = string.Format("select top 1 * from [myquan] where AdminID={0} and UserID={1} and State =0 and Man<={2} and getdate() between begindate and enddate order by Money desc", adminid, userid, man);
            DataRow dr = CommonDAL.GetDataRow(sql);
            return SimpleData(dr);
        }
        public static List<MyQuanInfo> FindCanUseQuan(int adminid, int userid, double man)
        {
            string sql = string.Format("select * from [myquan] where AdminID={0} and UserID={1} and State =0 and Man<={2} and getdate() between begindate and enddate order by Money asc", adminid, userid, man);
            DataTable dt = CommonDAL.GetDataTable(sql);
            return ListSimpleData(dt);
        }
        public static List<MyQuanInfo> FindPage(int userid, int state, int startindex, int pagesize, string order, string desc, out int count)
        {
            StringBuilder filter = new StringBuilder();
            filter.AppendFormat(" and [myquan].UserID={0}", userid);
            if (state == 2)
            {
                filter.AppendFormat(" and getdate() not between begindate and enddate");
            }
            else
            {
                filter.AppendFormat(" and [myquan].State ={0} and getdate() between begindate and enddate", state);
            }
            DataTable dt = CommonDAL.FindPage(startindex, pagesize, order, desc, filter, "[myquan]", out count);
            return ListSimpleData(dt);
        }
        public static List<MyQuanInfo> FindAll()
        {
            DataTable dt = CommonDAL.GetDataTable(string.Format("select * from [myquan]"));
            return ListSimpleData(dt);
        }
        private static MyQuanInfo SimpleData(DataRow dr)
        {
            MyQuanInfo info = new MyQuanInfo();
            if (dr != null)
            {
                info.ID = DataConvert.ToInt(dr["ID"]);
                info.AdminID = DataConvert.ToInt(dr["AdminID"]);
                info.UserID = DataConvert.ToInt(dr["UserID"]);
                info.Money = DataConvert.ToFloat(dr["Money"]);
                info.Man = DataConvert.ToFloat(dr["Man"]);
                info.State = DataConvert.ToInt(dr["State"]);
                info.BeginDate = DataConvert.ToDateTime(dr["BeginDate"]);
                info.EndDate = DataConvert.ToDateTime(dr["EndDate"]);
                info.CreateDate = DataConvert.ToDateTime(dr["CreateDate"]);
            }
            return info;
        }
        private static MyQuanInfo AllData(DataRow dr)
        {
            MyQuanInfo info = new MyQuanInfo();
            if (dr != null)
            {
                info.ID = DataConvert.ToInt(dr["ID"]);
                info.AdminID = DataConvert.ToInt(dr["AdminID"]);
                info.UserID = DataConvert.ToInt(dr["UserID"]);
                info.Money = DataConvert.ToFloat(dr["Money"]);
                info.Man = DataConvert.ToFloat(dr["Man"]);
                info.State = DataConvert.ToInt(dr["State"]);
                info.BeginDate = DataConvert.ToDateTime(dr["BeginDate"]);
                info.EndDate = DataConvert.ToDateTime(dr["EndDate"]);
                info.CreateDate = DataConvert.ToDateTime(dr["CreateDate"]);
            }
            return info;
        }
        private static List<MyQuanInfo> ListSimpleData(DataTable dt)
        {
            List<MyQuanInfo> list = new List<MyQuanInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(SimpleData(row));
            }
            return list;
        }
        private static List<MyQuanInfo> ListAllData(DataTable dt)
        {
            List<MyQuanInfo> list = new List<MyQuanInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(AllData(row));
            }
            return list;
        }
    }
}

